﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Estrutura;


namespace Banco
{

    /// <summary>
    /// Summary description for BD_Pessoa
    /// </summary>
    public class BD_Pessoa
    {
        #region variaveis_globais

        acessaDB.conectaDBMSSQLServer sqlServer = new conectaDBMSSQLServer();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Pessoa()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
        }
        public BD_Pessoa(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public System.Data.DataSet _retorna_Pessoa(
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string PES_DTNASCIMENTO,
                                                string PES_RG,
                                                string PES_CPF,
                                                string PROFISSAO_ID,
                                                string SOLICITANTE_ID
                                                )
        {

            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "''";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''";
            if (string.IsNullOrEmpty(PES_DTNASCIMENTO)) PES_DTNASCIMENTO = "''";
            if (string.IsNullOrEmpty(PES_RG)) PES_RG = "''";
            if (string.IsNullOrEmpty(PES_CPF)) PES_CPF = "''";
            if (string.IsNullOrEmpty(PROFISSAO_ID)) PROFISSAO_ID = "''";
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "''";

            string sql = "SELECT " +
                                  "PESSOA.PESSOA_ID, " +
                                  "PESSOA.PES_NOME, " +
                                  "PESSOA.PES_DTNASCIMENTO, " +
                                  "PESSOA.PES_RG, " +
                                  "PESSOA.PES_CPF, " +
                                  "PESSOA.PROFISSAO_ID, " +
                                  "PESSOA.SOLICITANTE_ID " +
                            "FROM PESSOA " +
                            "WHERE " +
                            "      (PESSOA.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = '') AND " +
                            "      (PESSOA.PES_NOME = " + PES_NOME + "  OR " + PES_NOME + " = '') AND " +
                            "      (PESSOA.PES_DTNASCIMENTO = " + PES_DTNASCIMENTO + "  OR " + PES_DTNASCIMENTO + " = '') AND " +
                            "      (PESSOA.PES_RG = " + PES_RG + "  OR " + PES_RG + " = '') AND " +
                            "      (PESSOA.PES_CPF = " + PES_CPF + "  OR " + PES_CPF + " = '') AND " +
                            "      (PESSOA.PROFISSAO_ID = " + PROFISSAO_ID + "  OR " + PROFISSAO_ID + " = '') AND " +
                            "      (PESSOA.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = '') ";

            return sqlServer.retornaDataSet(sql, conectionString);;
            

        }


        public List<ES_Pessoa> _obter_Pessoa(
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string PES_DTNASCIMENTO,
                                                string PES_RG,
                                                string PES_CPF,
                                                string PROFISSAO_ID,
                                                string SOLICITANTE_ID,
                                                string PES_FALECIDO,
                                                string ORDER_BY_CLAUSE
                                                )
        {

            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "0"; 
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (!string.IsNullOrEmpty(PES_DTNASCIMENTO)) PES_DTNASCIMENTO = Util.retonarAspas(Util.formataData(PES_DTNASCIMENTO, "dd/MM/yyyy"));
            if (string.IsNullOrEmpty(PES_RG)) PES_RG = "''"; else PES_RG = Util.retonarAspas(PES_RG + "%");
            if (string.IsNullOrEmpty(PES_CPF)) PES_CPF = "''"; else PES_CPF = Util.retonarAspas(PES_CPF + "%");
            if (string.IsNullOrEmpty(PROFISSAO_ID)) PROFISSAO_ID = "0";
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "0";
            if (string.IsNullOrEmpty(PES_FALECIDO)) PES_FALECIDO = "-1";
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "PESSOA.PES_NOME ";

            string sql = "SELECT " +
                                  "PESSOA.PESSOA_ID, " +
                                  "PESSOA.PES_NOME, " +
                                  "PESSOA.PES_DTNASCIMENTO, " +
                                  "PESSOA.PES_RG, " +
                                  "PESSOA.PES_CPF, " +
                                  "PESSOA.PROFISSAO_ID, " +
                                  "PESSOA.SOLICITANTE_ID, " +
                                  "ISNULL(PESSOA.PES_FALECIDO,0) PES_FALECIDO "+
                            "FROM PESSOA " +
                            "WHERE " +
                            "      (PESSOA.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = 0) AND " +
                            "      (PESSOA.PES_NOME LIKE " + PES_NOME + "  OR " + PES_NOME + " = '') AND ";
            if (!string.IsNullOrEmpty(PES_DTNASCIMENTO))
                sql +=      "      (PESSOA.PES_DTNASCIMENTO = " + PES_DTNASCIMENTO + "  OR " + PES_DTNASCIMENTO + " = '') AND ";

            sql += 
                            "      (PESSOA.PES_RG = " + PES_RG + "  OR " + PES_RG + " = '') AND " +
                            "      (PESSOA.PES_CPF = " + PES_CPF + "  OR " + PES_CPF + " = '') AND " +
                            "      (PESSOA.PROFISSAO_ID = " + PROFISSAO_ID + "  OR " + PROFISSAO_ID + " = 0) AND " +
                            "      (PESSOA.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = 0) AND "+
                            "      (PESSOA.PES_FALECIDO = " + PES_FALECIDO + "  OR " + PES_FALECIDO + " = -1)  "+
                            " ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Pessoa> lista = new List<ES_Pessoa>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Pessoa item = new ES_Pessoa();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    if (!string.IsNullOrEmpty(dr["PES_DTNASCIMENTO"].ToString()))
                        item.PES_DTNASCIMENTO = Util.formataData(dr["PES_DTNASCIMENTO"].ToString(), "dd/MM/yyyy");
                    item.PES_RG = dr["PES_RG"].ToString();
                    item.PES_CPF = dr["PES_CPF"].ToString();
                    item.PROFISSAO_ID = dr["PROFISSAO_ID"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();
                    item.PES_FALECIDO = (dr["PES_FALECIDO"].ToString() == "0") ? false : true;                    

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public List<ES_Pessoa> _obter_Pessoa_Email(
                                                    string ALUNO_ID,
                                                    bool ALU_BOLSISTA,
                                                    bool RESPONSAVEL,
                                                    string PES_NOME,                                  
                                                    string TURMA_ID,
                                                    string ORDER_BY_CLAUSE,
                                                    bool lista_emal = true
                                                )
        {

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "0";           
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "PES_NOME ";

            string sql = "SELECT DISTINCT ALUNO.ALUNO_ID, PESSOA.PES_NOME, PESSOA.PES_EMAIL, 'Aluno' AS TIPO_PESSOA "+
                         "FROM PESSOA  "+
                         "LEFT JOIN ALUNO ON ALUNO.PESSOA_ID = PESSOA.PESSOA_ID "+
                         "LEFT JOIN MATRICULA ON MATRICULA.ALUNO_ID = ALUNO.ALUNO_ID "+
                         "WHERE PESSOA.PES_EMAIL IS NOT NULL "+
                         "AND (MATRICULA.TURMA_ID = "+TURMA_ID+" OR "+TURMA_ID+" = 0) ";
            if (RESPONSAVEL)
                sql +=
                         "UNION ALL "+
                         "SELECT DISTINCT 0 AS ALUNO_ID, PESSOA.PES_NOME, PESSOA.PES_EMAIL , 'Responsável' AS TIPO_PESSOA "+
                         "FROM RESPONSAVEL_ALUNO "+
                         "INNER JOIN ALUNO ON RESPONSAVEL_ALUNO.ALUNO_ID = ALUNO.ALUNO_ID "+
                         "INNER JOIN PESSOA ON PESSOA.PESSOA_ID = RESPONSAVEL_ALUNO.RESPONSAVEL_ID "+
                         "INNER JOIN MATRICULA ON MATRICULA.ALUNO_ID = RESPONSAVEL_ALUNO.ALUNO_ID "+
                         "WHERE PESSOA.PES_EMAIL IS NOT NULL "+
                         "AND (MATRICULA.TURMA_ID = "+TURMA_ID+" OR "+TURMA_ID+" = 0) ";
                          
            sql +=
                         " ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Pessoa> lista = new List<ES_Pessoa>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    
                    ES_Pessoa item = new ES_Pessoa();
                    
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.PES_EMAIL = dr["PES_EMAIL"].ToString();
                    item.TIPO_PESSOA = dr["TIPO_PESSOA"].ToString();
                    

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public ES_Pessoa _obter_Pessoa_by_id(
                                                string PESSOA_ID,
                                                string PES_CPF = "0",
                                                string PES_NOME = "",
                                                string PES_DTNASCIMENTO = ""
                                                )
        {

            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "0";
            if (string.IsNullOrEmpty(PES_CPF) || PES_CPF == "00000000000") PES_CPF = "''"; else PES_CPF = Util.retonarAspas(PES_CPF);
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME);
            

            string sql = "SELECT " +
                                  "PESSOA.PESSOA_ID, " +
                                  "PESSOA.PES_NOME, " +
                                  "PESSOA.PES_DTNASCIMENTO, " +
                                  "PESSOA.PES_RG, " +
                                  "PESSOA.PES_CPF, " +
                                  "PESSOA.PROFISSAO_ID, " +
                                  "PESSOA.SOLICITANTE_ID, " +
                                  "PESSOA.PES_EMAIL, " +
                                  "ISNULL(PESSOA.PES_FALECIDO,0) PES_FALECIDO, " +
                                  "(SELECT PSC_TELEFONE FROM PESSOA_CONTATO WHERE PESSOA_CONTATO.PSC_TELEFONE_TIPO = 'R' AND PESSOA_CONTATO.PESSOA_ID = PESSOA.PESSOA_ID) PES_FONERESID, " +
                                  "(SELECT PSC_TELEFONE FROM PESSOA_CONTATO WHERE PESSOA_CONTATO.PSC_TELEFONE_TIPO = 'C' AND PESSOA_CONTATO.PESSOA_ID = PESSOA.PESSOA_ID) PES_FONECOMERC, " +
                                  "(SELECT PSC_TELEFONE FROM PESSOA_CONTATO WHERE PESSOA_CONTATO.PSC_TELEFONE_TIPO = 'M' AND PESSOA_CONTATO.PESSOA_ID = PESSOA.PESSOA_ID) PES_FONEMOVEL " +
                            "FROM PESSOA " +
                            "WHERE " +
                            "      (PESSOA.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = 0) AND " +
                            "      (PESSOA.PES_CPF = " + PES_CPF + "  OR " + PES_CPF + " = '') AND" +
                            "      (PESSOA.PES_NOME = " + PES_NOME + "  OR " + PES_NOME + " = '') ";
            if (!string.IsNullOrEmpty(PES_DTNASCIMENTO))
                sql += "      AND (PESSOA.PES_DTNASCIMENTO = " + Util.retonarAspas(Util.formataData(PES_DTNASCIMENTO, "dd/MM/yyyy")) + ") ";

            ES_Pessoa item = new ES_Pessoa();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.PES_DTNASCIMENTO = dr["PES_DTNASCIMENTO"].ToString();
                    item.PES_RG = dr["PES_RG"].ToString();
                    item.PES_CPF = dr["PES_CPF"].ToString();
                    item.PROFISSAO_ID = dr["PROFISSAO_ID"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();
                    item.PES_EMAIL = dr["PES_EMAIL"].ToString();
                    item.PES_FONERESID = dr["PES_FONERESID"].ToString();
                    item.PES_FONECOMERC = dr["PES_FONECOMERC"].ToString();
                    item.PES_FONEMOVEL = dr["PES_FONEMOVEL"].ToString();
                    item.PES_FALECIDO = (dr["PES_FALECIDO"].ToString() == "0") ? false : true;
                }
                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public void _inserir_Pessoa(
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string PES_DTNASCIMENTO,
                                                string PES_RG,
                                                string PES_CPF,
                                                string PROFISSAO_ID,
                                                string SOLICITANTE_ID,
                                                bool PES_FALECIDO
                                                )
        {

            string sql = " INSERT INTO PESSOA " +
                         "      ( " +
                         "          PES_NOME, " +
                         "          PES_DTNASCIMENTO, " +
                         "          PES_RG, " +
                         "          PES_CPF, " +
                         "          PROFISSAO_ID, " +
                         "          SOLICITANTE_ID,  " +
                         "          PES_FALECIDO "+
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                              Util.retonarAspas(PES_NOME, true) + ", " +
                              Util.retonarAspas(PES_DTNASCIMENTO, true) + ", " +
                              Util.retonarAspas(PES_RG, true) + ", " +
                              Util.retonarAspas(PES_CPF, true) + ", " +
                              Util.retonarAspas(PROFISSAO_ID, true) + ", " +
                              Util.retonarAspas(SOLICITANTE_ID, true) + ", " +
                             ((PES_FALECIDO == true) ? "1" : "0") + 
                 "      )";
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _atualizar_Pessoa(
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string PES_EMAIL,
                                                string PES_DTNASCIMENTO,
                                                string PES_RG,
                                                string PES_CPF,
                                                string PES_ESTADOCIVIL,
                                                string PROFISSAO_ID,
                                                string SOLICITANTE_ID,
                                                bool PES_FALECIDO
                                                )
        {
            if (PES_CPF == "00000000000") PES_CPF = "";

            string sql = " UPDATE PESSOA SET " +
                         "      PES_NOME = " + Util.retonarAspas(PES_NOME, true) + ", " +
                         "      PES_EMAIL = " + Util.retonarAspas(PES_EMAIL, true) + ", " +
                         "      PES_DTNASCIMENTO = " + Util.retonarAspas(Util.formataData(PES_DTNASCIMENTO,"dd/MM/yyyy", true), true) + ", " +
                         "      PES_RG = " + Util.retonarAspas(PES_RG, true) + ", " +
                         "      PES_CPF = " + Util.retonarAspas(PES_CPF, true) + ", " +
                         "      PES_ESTADOCIVIL = " + Util.retonarAspas(PES_ESTADOCIVIL, true) + ", " +
                         "      PROFISSAO_ID = " + Util.retonarNull(PROFISSAO_ID) + ", " +
                         "      SOLICITANTE_ID = " + Util.retonarNull(SOLICITANTE_ID) + ", " +
                         "      PES_FALECIDO = " +((PES_FALECIDO == true) ? "1" : "0") +
                         " WHERE PESSOA_ID =  " + PESSOA_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }


        public void _excluir_Pessoa(string PESSOA_ID)
        {
            string sql = "DELETE FROM PESSOA WHERE PESSOA_ID = " + PESSOA_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}